﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using FisheryPlatform.Entity;
using System.Data;
using FisheryPlatform.DataAccess.DBUtility;
using FisheryPlatform.DataAccess.Term;

namespace FisheryPlatform.DataAccess.Post
{
    public class PostsDB
    {
        #region private const
        private const string PARAM_POST_ID = "@PostId";
        private const string PARAM_AUTHOR_ID = "@AuthorId";
        private const string PARAM_TITLE = "@Title";
        private const string PARAM_CONTENT = "@Content";
        private const string PARAM_STATUS = "@Status";
        private const string PARAM_PASSWORD = "@Password";
        private const string PARAM_TYPE = "@Type";
        private const string PARAM_POST_DATE = "@PostDate";
        private const string PARAM_MODIFY_DATE = "@ModifyDate";

        private static readonly string ConnectionString = SQLHelper.CONNECTION_STRING;
        #endregion

        private static void FillEntityFrom(SqlDataReader reader, PostsEntity obj, bool isSearch)
        {
            #region
            if (reader != null && !reader.IsClosed)
            {
                obj.PostId = reader.IsDBNull(reader.GetOrdinal("PostId")) ? 0 : reader.GetInt64(reader.GetOrdinal("PostId"));
                obj.AuthorId = reader.IsDBNull(reader.GetOrdinal("AuthorId")) ? 0 : reader.GetInt64(reader.GetOrdinal("AuthorId"));
                obj.Title = reader.IsDBNull(reader.GetOrdinal("Title")) ? String.Empty : reader.GetString(reader.GetOrdinal("Title"));
                if (isSearch == false)
                {
                    obj.Content = reader.IsDBNull(reader.GetOrdinal("Content")) ? String.Empty : reader.GetString(reader.GetOrdinal("Content"));
                }
                obj.Status = reader.IsDBNull(reader.GetOrdinal("Status")) ? PostStatusEnum.Publish : (PostStatusEnum)reader.GetByte(reader.GetOrdinal("Status"));
                obj.Password = reader.IsDBNull(reader.GetOrdinal("Password")) ? String.Empty : reader.GetString(reader.GetOrdinal("Password"));
                obj.Type = reader.IsDBNull(reader.GetOrdinal("Type")) ? PostTypeEnum.Post : (PostTypeEnum)reader.GetByte(reader.GetOrdinal("Type"));
                obj.PostDate = reader.IsDBNull(reader.GetOrdinal("PostDate")) ? DateTime.MinValue : reader.GetDateTime(reader.GetOrdinal("PostDate"));
                obj.ModifyDate = reader.IsDBNull(reader.GetOrdinal("ModifyDate")) ? DateTime.MinValue : reader.GetDateTime(reader.GetOrdinal("ModifyDate"));
            }
            #endregion
        }

        private static void FillEntityFrom(SqlDataReader reader, PostMixEntity obj)
        {
            #region
            while (reader.Read())
            {
                FillEntityFrom(reader, obj.Post, false);
            }
            if (reader.NextResult())
            {
                while (reader.Read())
                {
                    var termInfo = new TermEntity();
                    TermsDB.FillEntityFrom(reader, termInfo);
                    obj.Terms.Add(termInfo);
                }
            }
            #endregion
        }

        private static SqlParameter[] GetPostsParams(PostsEntity obj)
        {
            #region
            SqlParameter[] dbParams ={					
					 SQLHelper.MakeParam(PARAM_POST_ID, SqlDbType.BigInt,0,obj.PostId),
					 SQLHelper.MakeParam(PARAM_AUTHOR_ID, SqlDbType.BigInt,0,obj.AuthorId),
					 SQLHelper.MakeParam(PARAM_TITLE, SqlDbType.NVarChar, 500,obj.Title),					
					 SQLHelper.MakeParam(PARAM_CONTENT, SqlDbType.Text,0,obj.Content),					
					 SQLHelper.MakeParam(PARAM_STATUS, SqlDbType.TinyInt,0,(int)obj.Status),
					 SQLHelper.MakeParam(PARAM_PASSWORD, SqlDbType.NVarChar, 50,obj.Password),					
					 SQLHelper.MakeParam(PARAM_TYPE, SqlDbType.TinyInt,0,(int)obj.Type),
					 SQLHelper.MakeParam(PARAM_POST_DATE, SqlDbType.DateTime,0,obj.PostDate),
					 SQLHelper.MakeParam(PARAM_MODIFY_DATE, SqlDbType.DateTime,0,obj.ModifyDate)
			};

            return dbParams;
            #endregion
        }

        public static bool Save(PostMixEntity obj)
        {
            #region
            try
            {
                //可以没有类别和标签
                //if (obj.Terms.Count == 0)
                //{
                //    return false;
                //}
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    #region
                    conn.Open();
                    SqlTransaction sqlTran = conn.BeginTransaction();

                    var affectNum = 0;
                    List<PostTermRelationshipsEntity> postTermRelList = new List<PostTermRelationshipsEntity>();
                    obj.Terms.ForEach(item =>
                    {
                        PostTermRelationshipsEntity ptr = new PostTermRelationshipsEntity();
                        ptr.PostId = obj.Post.PostId;
                        ptr.TermId = item.TermId;
                        postTermRelList.Add(ptr);
                    });

                    SqlParameter[] dbParams = GetPostsParams(obj.Post);

                    if (obj.Post.PostId == 0)
                    {
                        #region
                        obj.Post.PostId = Convert.ToInt32(SQLHelper.ExecuteScalar(sqlTran, CommandType.StoredProcedure, "Posts_InsertUpdate", dbParams));

                        if (obj.Post.PostId > 0)
                        {
                            affectNum = 1;
                            postTermRelList.ForEach(item => { item.PostId = obj.Post.PostId; });
                            if (PostTermRelationshipsDB.Save(obj.Post.PostId, postTermRelList, sqlTran))
                            {
                                affectNum = 1;
                            }
                            else
                            {
                                affectNum = 0;
                            }
                        }
                        #endregion
                    }
                    else
                    {
                        #region
                        affectNum = SQLHelper.ExecuteNonQuery(sqlTran, CommandType.StoredProcedure, "Posts_InsertUpdate", dbParams);
                        if (affectNum > 0)
                        {
                            affectNum = 0;
                            if (postTermRelList.Count > 0)
                            {
                                if (PostTermRelationshipsDB.Save(obj.Post.PostId, postTermRelList, sqlTran))
                                {
                                    affectNum = 1;
                                }
                            }
                            else
                            {
                                if (PostTermRelationshipsDB.DeleteByPostId(obj.Post.PostId, sqlTran))
                                {
                                    affectNum = 1;
                                }
                            }
                        }
                        else
                        {
                            affectNum = 0;
                        }
                        #endregion
                    }

                    #region
                    if (affectNum > 0)
                    {
                        sqlTran.Commit();
                    }
                    else
                    {
                        sqlTran.Rollback();
                    }

                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    #endregion

                    return affectNum > 0 ? true : false;
                    #endregion
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Posts-->InsertOrUpdate-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }

            return false;
            #endregion
        }

        public static List<PostsEntity> Search(string sqlWhere, int pageIndex, int pageSize, out int pageCount)
        {
            #region
            List<PostsEntity> result = new List<PostsEntity>();
            pageCount = 0;
            try
            {
                if (string.IsNullOrEmpty(sqlWhere))
                {
                    sqlWhere = " 1=1 ";
                }

                SqlParameter[] dbParams =
                {
                    SQLHelper.MakeParam("@sqlWhere", SqlDbType.NVarChar,0,sqlWhere),
                    SQLHelper.MakeParam("@pageIndex",SqlDbType.Int,0,pageIndex),
                    SQLHelper.MakeParam("@pageSize",SqlDbType.Int,0,pageSize),
                    SQLHelper.MakeOutParam("@pageCount", SqlDbType.Int,0,0)                    
                };
                using (SqlDataReader reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Posts_Search", dbParams))
                {
                    while (reader.Read())
                    {
                        PostsEntity model = new PostsEntity();
                        FillEntityFrom(reader, model, true);
                        result.Add(model);
                    }
                }
                pageCount = int.Parse(dbParams[3].Value.ToString());
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Posts-->Search-->" + e.ToString());
                ExceptionMessageDB.Record(ex);
            }
            return result;
            #endregion
        }

        public static bool DeleteByPostId(long postId)
        {
            #region
            try
            {
                SqlParameter[] dbParams = new SqlParameter[] { SQLHelper.MakeParam(PARAM_POST_ID, SqlDbType.BigInt, 0, postId) };

                return SQLHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Posts_DeleteByPostId", dbParams) > 0 ? true : false;

            }

            catch (Exception e)
            {
                Exception ex = new Exception("Posts-->DeleteByPostId-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }

            return false;
            #endregion
        }

        public static PostMixEntity GetByPostId(long postId)
        {
            #region
            SqlDataReader reader = null;
            PostMixEntity obj = new PostMixEntity();

            try
            {
                SqlParameter[] dbParams = new SqlParameter[] { SQLHelper.MakeParam(PARAM_POST_ID, SqlDbType.BigInt, 0, postId) };

                reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Posts_GetByPostId", dbParams);

                FillEntityFrom(reader, obj);
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Posts-->GetByPostId-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }

            return obj;
            #endregion
        }

        /// <summary>
        /// 获取各种状态下的Post数量
        /// </summary>
        /// <returns></returns>
        public static Dictionary<int, int> GetAmountGroupByStatus()
        {
            #region
            SqlDataReader reader = null;
            Dictionary<int, int> result = new Dictionary<int, int>();

            try
            {
                reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Posts_GetAmountGroupByStatus", null);

                while (reader.Read())
                {
                    result.Add(reader.GetByte(reader.GetOrdinal("status")), reader.GetInt32(reader.GetOrdinal("amount")));
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Posts-->GetAmountGroupByStatus-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }

            return result;
            #endregion
        }

        public static bool BatchUpdateStatus(string postIds, PostStatusEnum status)
        {
            #region
            try
            {
                SqlParameter[] dbParams = new SqlParameter[] { 
                    SQLHelper.MakeParam("@PostIds", SqlDbType.VarChar, 0, postIds),
                    SQLHelper.MakeParam(PARAM_STATUS, SqlDbType.TinyInt, 0, (int) status)
                };

                return SQLHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Posts_BatchUpdateStatus", dbParams) > 0 ? true : false;
            }

            catch (Exception e)
            {
                Exception ex = new Exception("Posts-->BatchUpdateStatus-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }

            return false;
            #endregion
        }

    }
}
